import logging
from datetime import datetime
from decimal import Decimal
from typing import Optional, List

from fastapi import APIRouter, Depends
from fastapi.params import Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

mrk_head_router = APIRouter(prefix="/mrkHead", tags=["耗材盘点损益管理"], dependencies=[Depends(oauth2_scheme)])


class MrkHeadSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fbilltype: str = Field(None, description="报废类型(1:仓库报废/2:储物柜报废)")
    fmrkuser: str = Field(None, description="报废人员")
    fstockno: str = Field(None, description="仓库编号")
    fbillno: str = Field(None, description="单号")
    fbillstate: str = Field(None, description="单据状态(1:未报废/2:已报废/8:已作废)")


@mrk_head_router.get("/list", summary="查询耗材盘点损益")
def list_mrk_head(mrk: MrkHeadSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_mrk_head where fbilltype=1"
    if mrk.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = mrk.fsiteNo
    if mrk.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart != "") and (mrk.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd

    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fsrcbillno,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.m_tc_mrk_head where fbilltype=1"
    if mrk.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = mrk.fsiteNo
    if mrk.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart != "") and (mrk.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd
    sql = sql + " order by  fmrkdate,fbillno  desc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = mrk.limit
    param["offset"] = mrk.offset

    data = db.execute(text(sql), param).fetchall()

    # 查找明细
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmaterialno,b.fbuyprice,b.fqty,b.ftotamt,b.fprocdate,b.fmemo"
    sql_3 = sql_3 + " from lims.m_tc_mrk_head a,lims.m_tc_mrk_detail b where a.fbillno=b.fbillno and fbilltype=1"
    if mrk.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = mrk.fsiteNo
    if mrk.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mrk.fbillno
    if mrk.fmrkuser != "":
        sql_3 = sql_3 + " and a.fmrkuser = :fmrkuser "
        param["fmrkuser"] = mrk.fmrkuser
    if mrk.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = mrk.fstockno
    if mrk.fbilltype != "":
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param["fbilltype"] = mrk.fbilltype
    if mrk.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = mrk.fbillstate
    if (mrk.ftakedateStart != "") and (mrk.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = mrk.ftakedateStart
        param["ftakedateEnd"] = mrk.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmrkuser'] = data[key][2]
        d1['fmrkdate'] = data[key][3]
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fsrcbillno'] = data[key][8]
        d1['fexplanation'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = data[key][12]
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        d1['fbilltype'] = data[key][15]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['ftotamt'] = data_detail[j1][5]
                d2['fprocdate'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": mrk.limit, "page": mrk.pageIndex,
                 "pages": (count + mrk.limit - 1) // mrk.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class LossDetailCreate(BaseModel):
    """耗材损益明细表数据模型"""
    fline: int = Field(description="行号", ge=-1)
    frfidbarcode: str = Field(default="", description="RFID条形码")
    fgoodno: str = Field(description="物品编号")
    fbuyprice: Decimal = Field(default=Decimal("0"), description="购买价格")
    fqty: Decimal = Field(default=Decimal("0"), description="数量")
    ftotamt: Decimal = Field(default=Decimal("0"), description="总金额")
    fboxbarcode: str = Field(default="", description="箱码")
    fcabbarcode: str = Field(default="", description="柜码")
    fprocdate: Optional[datetime] = Field(default=None, description="处理日期")
    foperator: str = Field(default="", description="操作人")
    fupddate: Optional[datetime] = Field(default=None, description="更新日期")
    fmemo: str = Field(default="", description="备注")


class LossHeadCreate(BaseModel):
    """耗材损益主表数据模型"""
    fbillno: str = Field(description="单据编号")
    fsiteno: str = Field(description="站点编号")
    fmrkuser: str = Field(description="标记人")
    fmrkdate: datetime = Field(description="标记日期")
    fstockno: str = Field(description="仓库编号")
    fitemcount: int = Field(default=-1, description="")
    fpiececount: int = Field(default=-1, description="")
    ftotalamt: float = Field(default=Decimal("0"), description="")
    fupddate: datetime = Field(default=datetime.now(), description="更新时间")
    fexplanation: str = Field(default="", description="说明")
    foperator: str = Field(description="操作人")
    fcheckdate: Optional[datetime] = Field(default=None, description="审核日期")
    fcheckuser: str = Field(default="", description="审核人")
    fbillstate: int = Field(default=-1, description="单据状态")
    fbilltype: int = Field(default=-1, description="单据类型")
    details: List[LossDetailCreate] = Field(description="损益明细列表")


@mrk_head_router.post("/create", summary="新增耗材损益")
def create_mrk_head(mrk: LossHeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            sql = "insert into lims.i_tc_mrk_head values "
            sql = sql + ("( :fbillno , :fsiteno , :fmrkuser , :fmrkdate , :fstockno , :fitemcount , :fpiececount ,"
                         " :ftotalamt , :fexplanation , :fupddate , :foperator , :fcheckdate, :fcheckuser , :fbillstate, :fbilltype )")
            db.execute(text(sql), {
                "fbillno": mrk.fbillno,
                "fsiteno": mrk.fsiteno,
                "fmrkuser": mrk.fmrkuser,
                "fmrkdate": mrk.fmrkdate,
                "fstockno": mrk.fstockno,
                "fitemcount": mrk.fitemcount,
                "fpiececount": mrk.fpiececount,
                "ftotalamt": mrk.ftotalamt,
                "fexplanation": mrk.fexplanation,
                "fupddate": mrk.fupddate,
                "foperator": mrk.foperator,
                "fcheckdate": mrk.fcheckdate,
                "fcheckuser": mrk.fcheckuser,
                "fbillstate": mrk.fbillstate,
                "fbilltype": mrk.fbilltype
            })
            noSet = set()
            num = Decimal(0)
            money = Decimal(0)

            details = mrk.details
            if details is not None and len(details) > 0:
                for item in details:
                    fbillno = fbillno
                    detailSql = "insert into lims.i_tc_mrk_detail values "
                    detailSql = detailSql + "( :fbillno , :fline , :frfidbarcode , :fgoodno , :fbuyprice , :fqty , :ftotamt , :fboxbarcode , :fcabbarcode , :fprocdate , :foperator , :fupddate, :fmemo )"
                    db.execute(text(detailSql), {
                        "fbillno": fbillno,
                        "fline": item.fline,
                        "frfidbarcode": item.frfidbarcode,
                        "fgoodno": item.fgoodno,
                        "fbuyprice": item.fbuyprice,
                        "fqty": item.fqty,
                        "ftotamt": item.ftotamt,
                        "fboxbarcode": item.fboxbarcode,
                        "fcabbarcode": item.fcabbarcode,
                        "fprocdate": item.fprocdate,
                        "foperator": item.foperator,
                        "fupddate": item.fupddate,
                        "fmemo": item.fmemo
                    })
                    noSet.add(item.fgoodno)
                    num = num + item.fqty
            fitemcount = len(noSet)
            fpiececount = num
            ftotalamt = money
            updateSql = "update lims.i_tc_mrk_head set  fitemcount = :fitemcount , fpiececount = :fpiececount ,ftotalamt = :ftotalamt "
            db.execute(text(updateSql), {
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
